package com.tuozixuan.onesql.mysql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.google.common.base.Enums;
import com.tuozixuan.onesql.db.Column;
import com.tuozixuan.onesql.db.ColumnTypeEnum;
import com.tuozixuan.onesql.db.Database;
import com.tuozixuan.onesql.db.Table;
import com.tuozixuan.onesql.db.TableTypeEnum;


public class BaseDao {
    
    public List<Database> getDatabaseList() {
        List<Database> databaseList = new ArrayList<>();
        try (Connection conn = JdbcUtils.getConnection()) {
            DatabaseMetaData databaseMetaData = conn.getMetaData();
            ResultSet rs = databaseMetaData.getCatalogs();
            Database database = null;
            while (rs.next()) {
                System.out.println(rs.getString("TABLE_CAT"));
                database = new Database();
                database.setName(rs.getString("TABLE_CAT"));
                databaseList.add(database);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } 
        return databaseList;
    }
    
    public List<Table> getTableList(String databaseName) {
        
        List<Table> tableList = new ArrayList<>();
        try (Connection conn = JdbcUtils.getConnection()) {
            DatabaseMetaData databaseMetaData = conn.getMetaData();
            ResultSet rs = databaseMetaData.getTables(databaseName, null, null, null);
            Table table = null;
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME"); //表名
                String tableType = rs.getString("TABLE_TYPE"); //表类型
                String remarks = rs.getString("REMARKS"); //表备注
                System.out.println("表名："+tableName + "   表类型： " + tableType + "   表注释：" + remarks);
                Enums.getIfPresent(TableTypeEnum.class, tableType);
                table = new Table();
                table.setName(tableName);
                table.setType(Enums.getIfPresent(TableTypeEnum.class, tableType).orNull());
                table.setRemarks(remarks);
                tableList.add(table);
                /**
                //表元数据
                ResultSetMetaData resultSetMetaData = rs.getMetaData();
                //遍历表所有元数据信息
                for (int i = 1; i <=  resultSetMetaData.getColumnCount(); i++) {
                    System.out.println(rs.getString(i)+"-==-"+ resultSetMetaData.getColumnName(i));
                }
                */
              
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } 
        return tableList;
    }
    
    public List<Column> getColumnList(String tableName) throws SQLException {
        List<Column> columnList = null;
        try (Connection conn = JdbcUtils.getConnection()) {
            DatabaseMetaData databaseMetaData = conn.getMetaData();
            columnList = this.getColumnList(conn, databaseMetaData, tableName);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } 
        return columnList;
    }
    
    public List<Column> getColumnList(Connection conn, DatabaseMetaData databaseMetaData, String tableName) throws SQLException {
        
        List<Column> columnList = new ArrayList<>();
        //表字段信息
        ResultSet resultSet = databaseMetaData.getColumns(conn.getCatalog(), databaseMetaData.getUserName(),tableName, null);
        Column column = null;
        while (resultSet.next()){
            //遍历表字段所有元数据
            /**
            for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
                System.out.println(resultSet.getMetaData().getColumnName(i+1)+":"+resultSet.getString(i+1));
            }*/
            System.out.println(resultSet.getString("TABLE_NAME")//表名
                    +"-"+resultSet.getString("column_name")//字段名
                    +"-"+resultSet.getString("TYPE_NAME")//字段类型
                    +"-"+resultSet.getString("DATA_TYPE")//字段类型
                    +"-"+resultSet.getString("COLUMN_SIZE")//长度
                    +"-"+resultSet.getString("DECIMAL_DIGITS")//长度
                    +"-"+resultSet.getString("COLUMN_DEF")//默认值
                    +"-"+resultSet.getString("REMARKS")//注释
                    +"-"+resultSet.getString("ORDINAL_POSITION")//字段位置
                    +"-"+resultSet.getString("IS_AUTOINCREMENT"));//是否自增
            
            column = new Column();
            column.setName(resultSet.getString("column_name"));
            column.setType(Enums.getIfPresent(ColumnTypeEnum.class, resultSet.getString("TYPE_NAME")).orNull());
            column.setSize(Integer.valueOf(resultSet.getString("COLUMN_SIZE")));
            column.setDefaultValue(resultSet.getString("COLUMN_DEF"));
            column.setRemarks(resultSet.getString("REMARKS"));
            column.setOrdinalPosition(Integer.valueOf(resultSet.getString("ORDINAL_POSITION")));
            column.setAutoIncrement("YES".equalsIgnoreCase(resultSet.getString("IS_AUTOINCREMENT")));
            columnList.add(column);
        }
        return columnList;
    }
    
    public List<Map<String,Object>> query(String sql) {
        List<Map<String,Object>> result = null;
        try (Connection connection = JdbcUtils.getConnection()) {
            QueryRunner queryRunner = new QueryRunner();
            result = queryRunner.query(connection,sql,new MapListHandler());
        } catch (Exception e) {
            throw new RuntimeException(e);
        } 
        return result;
    }
    
    public long queryCount(String sql) {
        long result = 0;
        try (Connection connection = JdbcUtils.getConnection()) {
            QueryRunner queryRunner = new QueryRunner();
            result = queryRunner.query(connection,sql, new ScalarHandler<Long>());
            //result = Integer.parseInt(String.valueOf(resultObj));
        } catch (Exception e) {
            throw new RuntimeException(e);
        } 
        return result;
    }
    
}